SQL Joins
In this lesson, we will highlight the different types of joins in SQL.
We'll cover the following
SQL JOIN#
A JOIN clause is used to combine rows from two or more tables, based on a common column.
We will be using the CUSTOMER and ORDER tables as shown below:
Customer Table
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Mark | 32 | Texas | 50,000 |
| 2 | John | 25 | NY | 65,000 |
| 3 | Emily | 23 | Ohio | 20,000 |
| 4 | Bill | 25 | Chicago | 75,000 |
| 5 | Tom | 27 | Washington | 35,000 |
| 6 | Jane | 22 | Texas | 45,000 |
Orders Table
| ORDER_ID | DATE | CUSTOMER_ID | AMOUNT |
|---|---|---|---|
| 100 | 2019-09-08 | 2 | 5000 |
| 101 | 2019-08-20 | 5 | 3000 |
| 102 | 2019-05-12 | 1 | 1000 |
| 103 | 2019-02-02 | 2 | 2000 |
Notice that the CUSTOMER_ID in the ORDER table references ID in the CUSTOMER table.
Now, what if we need to query something that is the combination of information in both tables?
For example, we want to:
- Find information on customers who ordered an item.
- Find the number of customers who ordered a certain item.
- Find the address of a customer in order to dispatch the order.
The joins in SQL can help you do that using the JOIN clause.
Different types of SQL JOINs#
Here are the three different types of the JOINs we will be discussing in this chapter:
- INNER JOIN / JOIN: Returns records that have matching values in both tables.
The values that are a match in both tables will be returned in the result-set.
- LEFT JOIN/ LEFT OUTER JOIN: Returns all records from the left table, and the matched records from the right table.
All the values from the left table and matching values will be returned in the result-set.
- RIGHT JOIN/ RIGHT OUTER: Returns all records from the right table, and the matched records from the left table.
All the values from the right table and matching values will be returned in the result-set.
In the next lesson, we will discuss the inner join in more detail.
Alias Syntax
INNER JOIN
Mark as Completed
Report an Issue
